/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dao.imp;

import dao.AirLineDAOException;
import dao.Conexao;
import dao.ReservaDAO;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import model.Reserva;
import java.util.ArrayList;
import java.util.List;
import model.Voo;
import model.Usuario;
/**
 *
 * @author Carlos, Edward, Diego, Honorio
 */
public class ReservaDAOImpl implements ReservaDAO {

     /**
     * Variavel para armazenar o objeto de conexao, enviado pelo Conexao.getConnection()
     */
    private Connection conn;

    /**
     * Construtor
     * @throws AirLineDAOException
     */
    public ReservaDAOImpl() throws AirLineDAOException {
        
        
        try {
            this.conn = Conexao.getConnection();
        } catch (Exception e) {
            throw new AirLineDAOException("Erro: " + ":\n" + e.getMessage());
        }
    }

    /**
     * Metodo chamado para executar query para excluir reserva na tabela
     * Reserva do banco de dados
     * @param reserva
     * @throws AirLineDAOException
     */
    public void excluirReserva(Reserva reserva) throws AirLineDAOException {
        PreparedStatement ps = null;
        Connection conn1 = null;
        if (reserva == null) {
            throw new AirLineDAOException("Nenhuma reserva foi informada!");
        }
        try {
            String sql = "DELETE FROM reserva WHERE numVooReserva=? AND idUser=?";
            conn1 = this.conn;
            ps = conn1.prepareStatement(sql);
            ps.setString(1, reserva.getNumVooReserva());
            ps.setString(2, reserva.getIdUser());
            ps.executeUpdate();
        } catch (SQLException e) {
            throw new AirLineDAOException("Erro ao excluir reserva " + e);
        } finally {
            Conexao.closeConnection(conn1, ps);
        }
    }

    /**
     * Metodo chamado para executar query para salvar reserva na tabela
     * Reserva do banco de dados
     * @param reserva
     * @throws AirLineDAOException
     */

    public void salvarReserva(Reserva reserva) throws AirLineDAOException {
        PreparedStatement ps = null;
        Connection conn1 = null;
        if (reserva == null)
            throw new AirLineDAOException("Nenhuma reserva foi informada!");
        try {
            String sql = "INSERT INTO reserva (numVooReserva, idUser)" +
                    "values (?, ?)";
            conn1 = this.conn;
            ps = conn1.prepareStatement(sql);
            ps.setString(1, reserva.getNumVooReserva());
            ps.setString(2, reserva.getIdUser());
            ps.executeUpdate();
        } catch (SQLException e){
            throw new AirLineDAOException("Erro ao inserir reserva " + e);
        } finally {
            Conexao.closeConnection(conn1, ps);
        }
    }
    
    public void salvarReserva(String numVooReserva, String idUser) throws AirLineDAOException {
        PreparedStatement ps = null;
        Connection conn1 = null;
        if (numVooReserva == null || idUser == null)
            throw new AirLineDAOException("Nenhuma reserva foi informada!");
        try {
            String sql = "INSERT INTO reserva (numVooReserva, idUser)" +
                    "values (?, ?)";
            conn1 = this.conn;
            ps = conn1.prepareStatement(sql);
            ps.setString(1, numVooReserva);
            ps.setString(2, idUser);
            ps.executeUpdate();
        } catch (SQLException e){
            throw new AirLineDAOException("Erro ao inserir reserva " + e);
        } finally {
            Conexao.closeConnection(conn1, ps);
        }
    }

    /**
     * Metodo chamado para executar query para buscar reserva na tabela
     * Reserva do banco de dados
     * @param reserva
     * @throws AirLineDAOException
     */

        public Reserva buscarReserva(Reserva reserva) throws AirLineDAOException {
        PreparedStatement ps = null;
        Connection conn1 = null;
        ResultSet rs = null;
        try {
            String sql = "SELECT * FROM reserva WHERE numVooReserva=? and idUser=?";
            conn1 = this.conn;
            ps = conn1.prepareStatement(sql);
            ps.setString(1, reserva.getNumVooReserva());
            ps.setString(2, reserva.getIdUser());
            rs = ps.executeQuery();
            if( !rs.next()) {
                throw new AirLineDAOException("Nao foi encontrado nenhuma reserva com os Dados " + reserva.getNumVooReserva() +
                        reserva.getIdUser());
            }
            String numVooReserva = rs.getString("numVooReserva");
            String idUser = rs.getString("idUser");

            Reserva reserva1 = new Reserva(numVooReserva, idUser);
            return reserva1;
        } catch (SQLException e){
            throw new AirLineDAOException("Erro ao procurar reserva " + e);
        } finally {
            Conexao.closeConnection(conn1, ps, rs);
        }


    }

        /**
     * Metodo chamado para executar query para listar reservas na tabela
     * Reserva do banco de dados
     * @param reserva
     * @throws AirLineDAOException
     */

    public List todasReservas() throws AirLineDAOException{
        PreparedStatement ps= null;
        Connection conn1= null;
        ResultSet rs= null;

        try{
            String sql= "Select * FROM reserva";
            conn1= this.conn;
            ps= conn1.prepareStatement(sql);
            rs= ps.executeQuery();
            List<Reserva> listReservas = new ArrayList<Reserva>();
            while(rs.next()){
                String numVooReserva= rs.getString("numVooReserva");
                String idUser= rs.getString("idUser");
                listReservas.add(new Reserva(numVooReserva, idUser));
            }return listReservas;
        }catch(SQLException e){
            throw new AirLineDAOException("Erro ao listar os reservas" + e);
        }finally{
            Conexao.closeConnection(conn1, ps, rs);
        }
    }

    public List reservaUser(Usuario usuario) throws AirLineDAOException{
        PreparedStatement ps= null;
        Connection conn1= null;
        ResultSet rs= null;

        try{
            String sql= "Select * FROM reserva WHERE idUser=?";
            conn1= this.conn;
            ps= conn1.prepareStatement(sql);
            //ps.setString(1, reserva.getNumVooReserva());
            ps.setString(1, usuario.getId());
            rs= ps.executeQuery();
            List<Reserva> listReservas = new ArrayList<Reserva>();
            while(rs.next()){
                String numVooReserva= rs.getString("numVooReserva");
                String idUser= rs.getString("idUser");
                listReservas.add(new Reserva(numVooReserva, idUser));
            }return listReservas;
        }catch(SQLException e){
            throw new AirLineDAOException("Erro ao listar os reservas" + e);
        }finally{
            Conexao.closeConnection(conn1, ps, rs);
        }
    }

    public List dadosReserva(Reserva reserva, Voo voo, Usuario usuario) throws AirLineDAOException{
        PreparedStatement ps= null;
        Connection conn1= null;
        ResultSet rs= null;

        try{
            String sql= " SELECT r.idUser, r.numVooReserva, u.nome, u.id, v.localOrigem, v.numVoo, v.localDestino, "
                    + "v.data, v.hora FROM reserva AS r, usuario AS u, voo AS v "
                    + "WHERE r.idUser=? AND r.numVooReserva=?";
            conn1= this.conn;
            ps= conn1.prepareStatement(sql);
            ps.setString(1, usuario.getId());
            ps.setString(2, voo.getNumVoo());
            rs= ps.executeQuery();
            List<Reserva> dadosReserva = new ArrayList<Reserva>();
            while(rs.next()){
                String numVooReserva= rs.getString("numVooReserva");
                String idUser= rs.getString("idUser");
                String nome= rs.getString("nome");
                String localOrigem= rs.getString("localOrigem");
                String localDestino= rs.getString("localDestino");
                String data= rs.getString("data");
                String hora= rs.getString("hora");
                dadosReserva.add(new Reserva(numVooReserva, idUser, nome, localOrigem, localDestino, data, hora));
            }return dadosReserva;
        }catch(SQLException e){
            throw new AirLineDAOException("Erro ao listar os reservas" + e);
        }finally{
            Conexao.closeConnection(conn1, ps, rs);
        }
    }

     public void atualizarReserva(Reserva reserva) throws AirLineDAOException {
        PreparedStatement ps = null;
        Connection conn1 = null;
        if (reserva == null) {
            throw new AirLineDAOException("Nenhuma reserva foi informado!");
        }
        try {
            String sql = "UPDATE reserva SET numVooReserva=?, idUser=?, ";
            conn1 = this.conn;
            ps = conn1.prepareStatement(sql);
            ps.setString(1, reserva.getNumVooReserva());
            ps.setString(2, reserva.getIdUser());
            ps.executeUpdate();
        } catch (SQLException e) {
            throw new AirLineDAOException("Erro ao atualizar reserva " + e);
        } finally {
            Conexao.closeConnection(conn1, ps);
        }
    }


}

